/*********************************************************************************
 *      Copyright:  (C) 2022 YanlinZhou<1222>
 *                  All rights reserved.
 *
 *       Filename:  sqlite.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(04/09/2022)
 *         Author:  YanlinZhou <1222>
 *      ChangeLog:  1, Release initial version on "04/09/2022 05:32:33 PM"
 *                 
 ********************************************************************************/

#include "sqlite.h"

sqlite3	*db = NULL;

int inquire_first_row(sqlite_ctx_t *sqlite_ctx)
 {
	int rv = -1;
	char sql[128] = {0};
	char *zErrMsg = NULL;
	char **dbResult = NULL;
	int nRow = 0;
	int nColumn = 0;

	sprintf(sql, "SELECT * FROM %s LIMIT 1;", sqlite_ctx->table_name);
	rv = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
	if(rv) 
	{
		printf("select first row failure:%d\n", *zErrMsg);
		sqlite3_free_table(dbResult);
		return -1;
	}
	if(nRow == 1) 
	{
		/* the database has data */
		sprintf(sqlite_ctx->db_str,"%s/%s/%s", dbResult[nColumn], dbResult[nColumn+1],dbResult[nColumn+2]);
//		printf("str  from database is : %s\n",str);
		return 1;
	}
	else 
	{
		/* the database is empty */
        //printf ("The database is empty!\n");
		return 0;
	}
 }

int inquire_first_row_msqt(sqlite_ctx_t *sqlite_ctx, pack_ctx_t *json, time_ctx_t *t)
 {
	 if(!sqlite_ctx || !json || !t)
	 {
		 printf("Invalid input parameter in inquire_first_row_msqt function\n ");
		 return -1;
	 }

	int rv = -1;
	char sql[128] = {0};
	char *zErrMsg = NULL;
	char **dbResult = NULL;
	int nRow = 0;
	int nColumn = 0;

	sprintf(sql, "SELECT * FROM %s LIMIT 1;", sqlite_ctx->table_name);
	rv = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
	if(rv) 
	{
		printf("select first row failure:%d\n", *zErrMsg);
		sqlite3_free_table(dbResult);
		return -1;
	}

	if(nRow == 1) 
	{
		char str[128];
		/* the database has data */
		sprintf(str,"%s/%s/%s", dbResult[nColumn], dbResult[nColumn+1],dbResult[nColumn+2]);
		strncpy(sqlite_ctx->db_str, str, sizeof(sqlite_ctx->db_str));
//		printf("str  from database is : %s\n",sqlite_ctx->db_str);
		
		json->payload = atof(dbResult[nColumn+1]);
		t->times = dbResult[nColumn+2];
		printf("The databas has data --> temperature: %f  times : %s \n", json->payload, t->times);
		return 1;
	}
	else 
	{
		/* the database is empty */
        //printf ("The database is empty!\n");
		return 0;
	}
 }

int delete_db_first_record(sqlite_ctx_t sqlite_ctx)
 {
    int rv = -1;
    char sql[128] = {0};
    char *zErrMsg =NULL;
    char **dbResult;
    int nRow=0, nColumn=0;    
    sprintf(sql, "delete from %s where rowid in ( select rowid from %s LIMIT 1)", sqlite_ctx.table_name, sqlite_ctx.table_name);
    rv = sqlite3_exec(db, sql, NULL, NULL,&zErrMsg);
    if(rv)
    {
        printf("delete failure:%s\n", zErrMsg);
        return -1;
    }
//    printf("delete database first record  sucessfully!\n");
    return 1;
}


int  client_save_data(sqlite_ctx_t sqlite_ctx, float temperature, char *time)
{
	char sql2[128] = {0};
	int rv = -1;
    char *zErrMsg =NULL;

    sprintf(sql2, "insert into %s values('%s','%f','%s')", sqlite_ctx.table_name, "No.1_Machine", temperature, time);
    rv = sqlite3_exec(db, sql2, NULL, NULL, &zErrMsg);

	if(rv < 0)
	{
		printf("insert data to database temp_tb failure!\n");
		return -1;
	}
	printf ("Client save the sample data into the temperature_tb sucessfully!\n");
	printf ("----------------------------------------------------------------\n");
	return 1;
}

int create_database(sqlite3 **db, const char *database_name)
{
	if(NULL == database_name || NULL == db)
	{
		printf("create_database function input argument is NULL!\n");
		return 0;
	}

    int             rv = -1;

     rv = sqlite3_open(database_name, db);
     if( rv )
     {
         printf("Opend database failure:%s\n", strerror(errno));
         sqlite3_close(*db);
         return -1;
     }
     else
     {
         printf("You have opened a sqlite3 database named temperature.db successfully!\n");
         return 0;
     }
}

int create_table(sqlite_ctx_t sqlite_ctx)
{
	if(sqlite_ctx.database_name == NULL)
	{
		printf("input argument is NULL!\n");
		return -1;
	}

    char sql[128] = {0};
    int rv = -1;
    char *zErrMsg =NULL;

     sprintf( sql, "CREATE TABLE IF NOT EXISTS %s(Piname TEXT, Temperature TEXT, Time TEXT);", sqlite_ctx.table_name);

     if ( sqlite3_exec(db, sql, NULL, NULL, &zErrMsg) != SQLITE_OK )
     {
         printf ("Creat table failure:%s\n", zErrMsg);
         return -1;
     }
     else
     {
         printf("Create database's table named temperature_tb  sucessfully!\n");
         return 0;
     }
}

#if 1

int create_database_and_table(sqlite_ctx_t sqlite_ctx)
{
	int rv = -1;
	/*   create_database */
	printf("sqlite_ctx.database_name:%s %s \n", sqlite_ctx.database_name, sqlite_ctx.table_name);
	if( (rv = create_database(&db, sqlite_ctx.database_name)) < 0 )
	{
		printf("create_database failure:%s\n");
		return -1;
	}

	/*   create_table */
	if( (rv = create_table(sqlite_ctx)) < 0 )
	{
		printf("create_table failure!\n");
		return -2;
	}
}

#endif
